package com.eorchis.module.statistics.trainingcompletionrate.dao.impl;

import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.springframework.stereotype.Repository;

import com.eorchis.constants.DeptConstants;
import com.eorchis.core.Contants;
import com.eorchis.core.basedao.dao.impl.HibernateAbstractBaseDao;
import com.eorchis.module.classuser.domain.ClassUser;
import com.eorchis.module.statistics.trainingcompletionrate.dao.ITrainingCompletionRateDao;
import com.eorchis.module.statistics.trainingcompletionrate.ui.commond.TrainingCompletionRateQueryCommond;
import com.eorchis.module.statistics.trainingcompletionrate.ui.commond.TrainingCompletionRateValidCommond;
import com.eorchis.utils.utils.PropertyUtil;

@Repository("com.eorchis.module.statistics.trainingcompletionrate.dao.impl.TrainingCompletionRateDaoImpl")
@SuppressWarnings("unchecked")
public class TrainingCompletionRateDaoImpl extends HibernateAbstractBaseDao
		implements ITrainingCompletionRateDao {

	/**
	 * @category 查询部委调训完成率统计
	 * @param ministrieId 部委ID
	 * @param ministrieName 部委名称
	 * @return
	 * @throws Exception
	 */
	public List<TrainingCompletionRateValidCommond> queryTrainingCompletionRateList(String ministrieId,String ministrieName) throws Exception{
		String sql = "select tc.ministrie_id, tc.ministrie_name, sum(tc.class_user_num) as classUserNum, " +
				" (select count(tcu.user_id) from training_class_user tcu where tcu.active_state = :ACTIVE_STATE" +
				" and tcu.auditing_state in (:auditingY) and tcu.class_id = tc.class_id) as actualUserCount, dep.order_num" +
				" from training_class tc left join base_department dep on tc.ministrie_id=dep.depid " +
				" where tc.active_state = :ACTIVE_STATE ";
//			       + " and tc.audit_state = :ACTIVE_STATE ";
		Map<String, Object> paramMap = new HashMap<String, Object>();
		paramMap.put("ACTIVE_STATE", Contants.ACTIVE_STATE_YES);
		paramMap.put("auditingY", new Integer[]{ClassUser.IS_AUDITING_Y,ClassUser.IS_AUDITING_Y_CX_N});//报名通过
		if(PropertyUtil.objectNotEmpty(ministrieId)){
			sql += " and tc.ministrie_id = :MINISTRIE_ID ";
			paramMap.put("MINISTRIE_ID", ministrieId);
		}
		if(PropertyUtil.objectNotEmpty(ministrieName)){
			sql += " and tc.ministrie_name like :MINISTRIE_NAME ";
			paramMap.put("MINISTRIE_NAME", "%"+ministrieName+"%");
		}
		sql += " group by tc.ministrie_id, tc.ministrie_name, dep.order_num, tc.class_id ";
		sql = "select t.ministrie_id as \"ministrieId\", t.ministrie_name as \"ministrieName\"," +
				" sum(t.classUserNum) as \"planUserCount\", sum(t.actualUserCount) as \"actualUserCount\"" +
				" from (" + sql + ") t group by t.ministrie_id, t.ministrie_name, t.order_num order by t.order_num";
		return (List<TrainingCompletionRateValidCommond>) executeFind(QueryStringType.SQL, sql, paramMap,TrainingCompletionRateValidCommond.class);
	}
	
	/**
	 * @category 查询部委调训完成率统计
	 * @param deptId
	 * @param ministrieId
	 * @param ministrieName
	 * @return
	 * @throws Exception
	 */
	public List<TrainingCompletionRateValidCommond> queryTrainingCompletionRateList(String deptId,String ministrieId,String ministrieName) throws Exception{
		// 报名单位下用户
		String unitUserSql = "select du.depid, d.depname, du.userid "
				+ " from base_department_user du, "
				+ " (select depid, depname, active_state "
				+ "  from base_department "
				+ "  start with parent_id = :SIGN_UNIT_ROOT "
				+ "  connect by prior depid = parent_id) d "
				+ "  where du.depid = d.depid " 
				+ "  and du.active_state = :ACTIVE_STATE "
				+ "  and d.active_state = :ACTIVE_STATE";
		// 报名成功的班级用户及所在班级部委
		String classUserSql = "select u.user_id,tc.ministrie_id, tc.ministrie_name "
				+ " from training_class_user u "
				+ " left join training_class tc "
				+ " on u.class_id = tc.class_id "
				+ " where u.active_state = :ACTIVE_STATE " 
				+ " and u.auditing_state in (:auditingY) "
				+ " and tc.active_state = :ACTIVE_STATE "
				+ " and tc.publish_state = :ACTIVE_STATE ";
//				+ " and tc.audit_state = :ACTIVE_STATE ";
		// 报名单位成功报名用户统计
		String unitSignSql = "select t1.depid, t1.depname,t2.ministrie_id,t2.ministrie_name,count(t2.user_id) userCount "
				+ " from ({unitUserSql}) t1 "
				+ " left join ({classUserSql}) t2 "
				+ " on t1.userid = t2.user_id "
				+ " where 1=1 ";
		//单位报名班级所在部委
		String deptMinistrieSql = "select tcpa.dept_id, "
		       +" tc.ministrie_id, "
		       +" tc.ministrie_name, "
		       +" sum(tcpa.plan_count) as plancount "
		       +" from training_class_plan_apply tcpa, training_class tc "
		       +" where tcpa.class_id = tc.class_id ";
		// 构造查询条件
		Map<String,Object> paramMap = new HashMap<String,Object>();
		paramMap.put("ACTIVE_STATE", Contants.ACTIVE_STATE_YES);//活动状态
		paramMap.put("auditingY", new Integer[]{ClassUser.IS_AUDITING_Y,ClassUser.IS_AUDITING_Y_CX_N});//报名通过
		paramMap.put("SIGN_UNIT_ROOT", DeptConstants.SIGN_DEPT_ROOT_ID);//报名单位根节点
		if(PropertyUtil.objectNotEmpty(deptId)){
			deptMinistrieSql += " and tcpa.dept_id=:DEPT_ID";
			unitUserSql += " and du.depid=:DEPT_ID";
			paramMap.put("DEPT_ID", deptId);
		}
		if(PropertyUtil.objectNotEmpty(ministrieId)){
			unitSignSql += " and t2.ministrie_id=:MINISTRIE_ID";
			deptMinistrieSql += " and tc.ministrie_id=:MINISTRIE_ID";
			paramMap.put("MINISTRIE_ID", ministrieId);
		}
		if(PropertyUtil.objectNotEmpty(ministrieName)){
			unitSignSql += " and t2.ministrie_name=:MINISTRIE_NAME";
			deptMinistrieSql += " and tc.ministrie_name=:MINISTRIE_NAME";
			paramMap.put("MINISTRIE_NAME", ministrieName);
		}
		String finalSql = "select d1.ministrie_id as \"ministrieId\","
				+ " d1.ministrie_name as \"ministrieName\","
				+ " d1.plancount as \"planUserCount\","
				+ " d2.userCount as \"actualUserCount\" "
				+ " from ({deptMinistrieSql}) d1 "
				+ " left join ({unitSignSql}) d2 on d1.dept_id=d2.depid "
				+ " left join base_department dep on d1.ministrie_id=dep.depid "
				+ " order by dep.order_num";
		unitSignSql += " group by t1.depid, t1.depname,t2.ministrie_id,t2.ministrie_name ";
		deptMinistrieSql += " group by tc.ministrie_id, tc.ministrie_name, tcpa.dept_id ";
		// 替换变量
		finalSql = finalSql.replace("{unitSignSql}", unitSignSql)
				.replace("{deptMinistrieSql}", deptMinistrieSql)
				.replace("{unitUserSql}", unitUserSql)
				.replace("{classUserSql}", classUserSql);
		return (List<TrainingCompletionRateValidCommond>) executeFind(QueryStringType.SQL, finalSql, paramMap,TrainingCompletionRateValidCommond.class);
	}
	@Override
	public List<TrainingCompletionRateValidCommond> queryTrainingCompletionRateList(
			TrainingCompletionRateQueryCommond queryCommond) {
		// 查询报名单位计划报名人数
		String planSql = "select t.dept_id,t.dept_name, sum(t.plan_count) as planCount "
				+ " from training_class_plan_apply t,training_class tt  " 
				+ " where t.class_id=tt.class_id  and tt.publish_state = 1  ";
		// 报名单位下用户
		String unitUserSql = "select du.depid, d.depname, du.userid "
				+ " from base_department_user du, "
				+ " (select depid, depname, active_state "
				+ "  from base_department "
				+ "  start with parent_id = :SIGN_UNIT_ROOT "
				+ "  connect by prior depid = parent_id) d "
				+ "  where du.depid = d.depid " 
				+ "  and du.active_state = :ACTIVE_STATE "
				+ "  and d.active_state = :ACTIVE_STATE";
		// 报名成功的班级用户
		String classUserSql = "select u.user_id "
				+ " from training_class_user u "
				+ " left join training_class tc "
				+ " on u.class_id = tc.class_id "
				+ " where u.active_state = :ACTIVE_STATE " 
				+ " and u.auditing_state in (:auditingY) "
				+ " and tc.active_state = :ACTIVE_STATE "
				+ " and tc.publish_state = :ACTIVE_STATE ";
//				+ " and tc.audit_state = :ACTIVE_STATE ";
		// 报名单位成功报名用户统计
		String unitSignSql = "select t1.depid, t1.depname, count(t2.user_id) userCount "
				+ " from ({unitUserSql}) t1 "
				+ " left join ({classUserSql}) t2 "
				+ " on t1.userid = t2.user_id "
				+ " group by t1.depid, t1.depname";
		// 最终查询sql
		String finalSql = "select d1.dept_id as \"signUnitId\","
				+ " d1.dept_name as \"signUnitName\", "
				+ " decode(d1.planCount,null,0,d1.planCount) as \"planUserCount\", "
				+ " decode(d2.userCount,null,0,d2.userCount) as \"actualUserCount\" "
				+ " from ({planSql}) d1 "
				+ " left join ({unitSignSql}) d2 "
				+ " on d1.dept_id = d2.depid " 
				+ " left join base_department dep on d1.dept_id=dep.depid "
				+ " order by dep.order_num";
		// 构造查询条件
		Map<String,Object> paramMap = new HashMap<String,Object>();
		if (PropertyUtil.objectNotEmpty(queryCommond.getSearchMinistrieID())) {// 部委ID
			classUserSql += " and tc.ministrie_id=:MINISTRIE_ID ";
			planSql += " and tt.ministrie_id=:MINISTRIE_ID ";
			paramMap.put("MINISTRIE_ID", queryCommond.getSearchMinistrieID());
		}
		if (PropertyUtil.objectNotEmpty(queryCommond.getSearchSignUnitID())) {// 报名单位ID
			planSql += " and t.dept_id=:DEPT_ID ";
			paramMap.put("DEPT_ID", queryCommond.getSearchSignUnitID());
		}
		if (PropertyUtil.objectNotEmpty(queryCommond.getSearchSignUnitName())) {// 报名单位名称
			planSql += " and t.dept_name like :DEPT_NAME ";
			paramMap.put("DEPT_NAME", "%"+queryCommond.getSearchSignUnitName()+"%");
		}
		/*if (PropertyUtil.objectNotEmpty(queryCommond.getSearchSignUnitCode())) {// 报名单位编码-简称
			planSql += " and t.dept_code=:DEPT_CODE ";
			paramMap.put("DEPT_CODE", queryCommond.getSearchSignUnitCode());
		}*/
		planSql += " group by t.dept_id, t.dept_name ";// 增加group
		// 替换变量
		finalSql = finalSql.replace("{unitSignSql}", unitSignSql)
				.replace("{planSql}", planSql)
				.replace("{unitUserSql}", unitUserSql)
				.replace("{classUserSql}", classUserSql);
		
		paramMap.put("ACTIVE_STATE", Contants.ACTIVE_STATE_YES);//活动状态
		paramMap.put("auditingY", new Integer[]{ClassUser.IS_AUDITING_Y,ClassUser.IS_AUDITING_Y_CX_N});//报名通过
		paramMap.put("SIGN_UNIT_ROOT", DeptConstants.SIGN_DEPT_ROOT_ID);//报名单位根节点
		return (List<TrainingCompletionRateValidCommond>) executeFind(QueryStringType.SQL, finalSql, paramMap,TrainingCompletionRateValidCommond.class);
	}

}
